from exceptiongroup import catch
from flask import Flask, render_template, request, g, redirect, url_for,session, jsonify
from SqlOp import connection_db
from utils.response import success_response, error_response

app = Flask(__name__)
app.secret_key = "scoresystem"

@app.before_request
def before_request():
    g.db = connection_db()
    if g.db is None: # 检查连接是否成功
        g.db_error = True
        print("数据库连接失败，请求可能无法正常处理")
    else:
        g.db_error = False
@app.teardown_request
def teardown_request(exception):
    db = getattr(g, 'db', None) # 安全地获取 g.db
    if db is not None:
        db.close()


@app.route("/")
def index():
    return redirect(url_for('admin_login'))


@app.route("/admin/logout")
def admin_logout():
    session.pop('username', None)
    return redirect(url_for('admin_login'))


# 用户登录
@app.route("/admin/login")
def admin_login():
    return render_template("login/adminLogin.html",info="登录页")
@app.route("/login_post",methods=['POST'])
def admin_loginop():

    if request.method == "POST":
        uname = ""
        sno = ""
        upwd = request.form.get("password")
        role = request.form.get("role")

        errors = {}
        form_data = {
            'username': request.form.get("username", ""),
            'sno': request.form.get("sno", ""),
            'password': upwd,
            'role': role
        }

        if not upwd:
            errors['password'] = "密码不能为空"

        if role == "0":
            sno = request.form.get("sno")
            if not sno:
                errors['username'] = "学号不能为空"
        elif role == "1" or role == '2':
            uname = request.form.get("username")
            if not uname:
                errors['username'] = "用户名不能为空"

        if errors:
            return render_template("login/adminLogin.html", errors=errors, form_data=form_data)

        cursor = g.db.cursor()
        count = 0
        pwddb = ""
        user_info = None

        if role == "0":
            selectSql = 'select sno, name, password from student where sno=%s'
            cursor.execute(selectSql, (sno,))
            user_info = cursor.fetchone()
            if user_info:
                count = 1
                pwddb = user_info[2]

        elif role == "1" or role == '2':
            selectSql = 'select id, username, password from admin where username=%s and role=%s'
            cursor.execute(selectSql, (uname,role))
            user_info = cursor.fetchone()
            if user_info:
                count = 1
                pwddb = user_info[2]

        cursor.close()
        if count == 0:
            if role == "0":
                errors['username'] = "学号不存在"
            else:
                errors['username'] = "用户名不存在"
            return render_template("login/adminLogin.html", errors=errors, form_data=form_data)
        else:
            if upwd == pwddb:
                session.clear()
                session['role'] = role

                if role == "0":
                    session['sno'] = user_info[0]
                    session['name'] = user_info[1] # 存储学生姓名

                elif role == "1" or role == '2':
                    session['username'] = user_info[1] # 存储管理员用户名

                return redirect(url_for('admin_home'))
            else:
                errors['password'] = "密码错误"
                return render_template("login/adminLogin.html", errors=errors, form_data=form_data)


# 用户注册
@app.route("/admin/register")
def admin_register():
    classList = []
    cursor = g.db.cursor()
    try:
        sql = '''
        select * from class ORDER BY 3, 2
        '''
        cursor.execute(sql)
        for row in cursor.fetchall():
            classList.append(
                {
                    'id': row[0],
                    'class':row[2] + row[1] ,
                }
            )
    except Exception as e:
        print(f"查询班级列表时出错: {e}")
    finally:
        cursor.close()
    return render_template("login/adminRegister.html",info="注册页",classList = classList)
@app.route("/register_post",methods=['POST'])
def admin_registerop():
    if request.method == "POST":
        name = request.form.get("name")
        upwd = request.form.get("password")
        sno = request.form.get("sno")
        role = request.form.get("role")
        classes = request.form.get("class")

        if role == '0':
            if sno is None or upwd is None or classes is None:
                return render_template("login/adminLogin.html", message="注册失败：学号、密码、班级不能为空", message_type="error")
            else:
                try:
                    sql_insert = '''
                    insert into student(sno,password,class_id,name)values(%s,%s,%s,%s)
                    '''
                    cursor = g.db.cursor()
                    cursor.execute(sql_insert, (sno, upwd, classes,name))
                    g.db.commit()
                    cursor.close()
                    return render_template("login/adminLogin.html", message="注册成功！请登录", message_type="success")
                except Exception as e:
                    return render_template("login/adminLogin.html", message="注册失败：" + str(e), message_type="error")



# 首页
@app.route("/admin/home")
def admin_home():
    if 'username' not in session and 'sno' not in session:
        return redirect(url_for('admin_login'))

    return render_template("home.html")

# 获取当前用户信息
@app.route("/api/user/info", methods=['GET'])
def api_get_user_info():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    user_info = {
        'role': session.get('role'),
        'username': session.get('username'),
        'sno': session.get('sno'),
        'name': session.get('name')
    }

    return success_response(msg="获取用户信息成功", data=user_info)


# 学生管理
@app.route("/admin/student")
def admin_student():
    return render_template("admin/student.html")
# 获取所有学生（分页查询）
@app.route("/api/students", methods=['GET'])
def api_get_students():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('page_size', 10))
        search = request.args.get('search', '').strip()
        class_filter = request.args.get('class_id', '').strip()

        page = max(1, page)
        page_size = min(max(1, page_size), 100)

        cursor = g.db.cursor()

        where_conditions = []
        params = []

        if search:
            where_conditions.append("(s.sno LIKE %s OR s.name LIKE %s)")
            search_param = f"%{search}%"
            params.extend([search_param, search_param])

        if class_filter:
            where_conditions.append("s.class_id = %s")
            params.append(int(class_filter))

        where_clause = ""
        if where_conditions:
            where_clause = "WHERE " + " AND ".join(where_conditions)

        count_sql = f"SELECT COUNT(*) FROM student s {where_clause}"
        cursor.execute(count_sql, params)
        total_count = cursor.fetchone()[0]

        total_pages = (total_count + page_size - 1) // page_size
        offset = (page - 1) * page_size

        data_sql = f"""
        SELECT s.id, s.sno, s.name, s.class_id, s.password, s.created_time, s.updated_time,
               c.name as class_name, c.grade as class_grade
        FROM student s
        LEFT JOIN class c ON s.class_id = c.id
        {where_clause}
        ORDER BY s.created_time DESC
        LIMIT %s OFFSET %s
        """
        cursor.execute(data_sql, params + [page_size, offset])

        students = []
        for row in cursor.fetchall():
            students.append({
                'id': row[0],
                'sno': row[1],
                'name': row[2],
                'class_id': row[3],
                'password': row[4],
                'created_time': row[5].strftime('%Y-%m-%d %H:%M:%S') if row[5] else None,
                'updated_time': row[6].strftime('%Y-%m-%d %H:%M:%S') if row[6] else None,
                'class_name': row[7],
                'class_grade': row[8],
                'class_display': f"{row[8]}级{row[7]}" if row[7] and row[8] else "未分配班级"
            })

        cursor.close()
        result_data = {
            'items': students,
            'pagination': {
                'current_page': page,
                'page_size': page_size,
                'total_count': total_count,
                'total_pages': total_pages,
                'has_prev': page > 1,
                'has_next': page < total_pages
            }
        }

        return success_response(msg="获取学生列表成功", data=result_data)
    except Exception as e:
        print(f"获取学生列表出错: {e}")
        return error_response(msg="获取学生列表失败", error=str(e))
# 添加学生
@app.route("/api/students", methods=['POST'])
def api_add_student():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        data = request.get_json()

        if not data.get('sno') or not data.get('name') or not data.get('class_id') or not data.get('password'):
            return error_response(msg="数据验证失败", error="学号、姓名、班级和密码为必填项")

        cursor = g.db.cursor()

        # 检查学号是否已存在
        check_sql = "SELECT id FROM student WHERE sno = %s"
        cursor.execute(check_sql, (data['sno'],))
        existing_student = cursor.fetchone()
        if existing_student:
            cursor.close()
            return error_response(msg="添加学生失败", error=f"学号 {data['sno']} 已存在")

        # 检查班级是否存在
        class_check_sql = "SELECT id FROM class WHERE id = %s"
        cursor.execute(class_check_sql, (data['class_id'],))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="添加学生失败", error="所选班级不存在")

        # 插入新学生记录
        insert_sql = """
        INSERT INTO student (sno, name, class_id, password, role, created_time, updated_time)
        VALUES (%s, %s, %s, %s, '0', NOW(), NOW())
        """
        cursor.execute(insert_sql, (data['sno'], data['name'], data['class_id'], data['password']))

        student_id = cursor.lastrowid
        g.db.commit()
        cursor.close()

        new_student = {
            'id': student_id,
            'sno': data['sno'],
            'name': data['name'],
            'class_id': data['class_id'],
            'password': data['password']
        }

        return success_response(msg="学生添加成功", data=new_student)

    except Exception as e:
        print(f"添加学生出错: {e}")
        error_msg = str(e)
        return error_response(msg="添加学生失败", error=error_msg)
# 更新学生
@app.route("/api/students/<int:student_id>", methods=['PUT'])
def api_update_student(student_id):
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        data = request.get_json()

        if not data.get('sno') or not data.get('name') or not data.get('class_id') or not data.get('password'):
            return error_response(msg="数据验证失败", error="学号、姓名、班级和密码为必填项")

        cursor = g.db.cursor()

        # 检查学生是否存在
        check_sql = "SELECT id FROM student WHERE id = %s"
        cursor.execute(check_sql, (student_id,))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="更新失败", error="学生不存在")

        # 检查学号是否与其他学生冲突
        conflict_sql = "SELECT id FROM student WHERE sno = %s AND id != %s"
        cursor.execute(conflict_sql, (data['sno'], student_id))
        conflicting_student = cursor.fetchone()
        if conflicting_student:
            cursor.close()
            return error_response(msg="更新学生失败", error=f"学号 {data['sno']} 已存在")

        # 检查班级是否存在
        class_check_sql = "SELECT id FROM class WHERE id = %s"
        cursor.execute(class_check_sql, (data['class_id'],))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="更新学生失败", error="所选班级不存在")

        # 执行更新
        update_sql = """
        UPDATE student
        SET sno = %s, name = %s, class_id = %s, password = %s, updated_time = NOW()
        WHERE id = %s
        """
        cursor.execute(update_sql, (data['sno'], data['name'], data['class_id'], data['password'], student_id))

        g.db.commit()
        cursor.close()

        updated_student = {
            'id': student_id,
            'sno': data['sno'],
            'name': data['name'],
            'class_id': data['class_id'],
            'password': data['password']
        }

        return success_response(msg="学生更新成功", data=updated_student)

    except Exception as e:
        print(f"更新学生出错: {e}")
        error_msg = str(e)
        return error_response(msg="更新学生失败", error=error_msg)
# 删除学生
@app.route("/api/students/<int:student_id>", methods=['DELETE'])
def api_delete_student(student_id):
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        check_sql = "SELECT id FROM student WHERE id = %s"
        cursor.execute(check_sql, (student_id,))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="删除失败", error="学生不存在")

        # 检查是否有相关的成绩记录
        score_check_sql = "SELECT COUNT(*) FROM score WHERE student_id = %s"
        cursor.execute(score_check_sql, (student_id,))
        score_count = cursor.fetchone()[0]

        if score_count > 0:
            # 如果有成绩记录，先删除相关成绩
            delete_scores_sql = "DELETE FROM score WHERE student_id = %s"
            cursor.execute(delete_scores_sql, (student_id,))

        # 删除学生
        delete_sql = "DELETE FROM student WHERE id = %s"
        cursor.execute(delete_sql, (student_id,))

        g.db.commit()
        cursor.close()

        message = f'学生删除成功'
        if score_count > 0:
            message += f'，同时删除了 {score_count} 条相关成绩记录'

        delete_result = {
            'student_id': student_id,
            'deleted_scores_count': score_count
        }

        return success_response(msg=message, data=delete_result)

    except Exception as e:
        print(f"删除学生出错: {e}")
        return error_response(msg="删除学生失败", error=str(e))
# 获取班级选项列表
@app.route("/api/classes/options", methods=['GET'])
def api_get_class_options():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        sql = """
        SELECT id, name, grade
        FROM class
        ORDER BY grade DESC, name ASC
        """
        cursor.execute(sql)

        classes = []
        for row in cursor.fetchall():
            classes.append({
                'id': row[0],
                'name': row[1],
                'grade': row[2],
                'display': f"{row[2]}级{row[1]}"
            })

        cursor.close()
        return success_response(msg="获取班级选项成功", data=classes)

    except Exception as e:
        print(f"获取班级选项出错: {e}")
        return error_response(msg="获取班级选项失败", error=str(e))




# 成绩管理
@app.route("/admin/score")
def admin_score():
    return render_template("admin/score.html")

# 获取所有成绩（分页查询）
@app.route("/api/scores", methods=['GET'])
def api_get_scores():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('page_size', 10))
        search = request.args.get('search', '').strip()
        class_filter = request.args.get('class_id', '').strip()
        course_filter = request.args.get('course_id', '').strip()

        page = max(1, page)
        page_size = min(max(1, page_size), 100)

        cursor = g.db.cursor()

        where_conditions = []
        params = []

        # 如果是学生登录(role=0)，只能查看自己的成绩
        if session.get('role') == '0':
            where_conditions.append("s.sno = %s")
            params.append(session.get('sno'))

        if search:
            where_conditions.append("(s.sno LIKE %s OR s.name LIKE %s)")
            search_param = f"%{search}%"
            params.extend([search_param, search_param])

        if class_filter:
            where_conditions.append("s.class_id = %s")
            params.append(int(class_filter))

        if course_filter:
            where_conditions.append("sc.course_id = %s")
            params.append(int(course_filter))

        where_clause = ""
        if where_conditions:
            where_clause = "WHERE " + " AND ".join(where_conditions)

        count_sql = f"""
        SELECT COUNT(*) FROM score sc
        LEFT JOIN student s ON sc.student_id = s.id
        LEFT JOIN course c ON sc.course_id = c.id
        LEFT JOIN class cl ON s.class_id = cl.id
        {where_clause}
        """
        cursor.execute(count_sql, params)
        total_count = cursor.fetchone()[0]

        total_pages = (total_count + page_size - 1) // page_size
        offset = (page - 1) * page_size

        data_sql = f"""
        SELECT sc.id, sc.student_id, sc.course_id, sc.score, sc.exam_date, sc.remark,
               sc.created_time, sc.updated_time,
               s.sno, s.name as student_name, s.class_id,
               c.name as course_name, c.code as course_code,
               cl.name as class_name, cl.grade as class_grade
        FROM score sc
        LEFT JOIN student s ON sc.student_id = s.id
        LEFT JOIN course c ON sc.course_id = c.id
        LEFT JOIN class cl ON s.class_id = cl.id
        {where_clause}
        ORDER BY sc.created_time DESC
        LIMIT %s OFFSET %s
        """
        cursor.execute(data_sql, params + [page_size, offset])

        scores = []
        for row in cursor.fetchall():
            scores.append({
                'id': row[0],
                'student_id': row[1],
                'course_id': row[2],
                'score': float(row[3]) if row[3] else None,
                'exam_date': row[4].strftime('%Y-%m-%d') if row[4] else None,
                'remark': row[5],
                'created_time': row[6].strftime('%Y-%m-%d %H:%M:%S') if row[6] else None,
                'updated_time': row[7].strftime('%Y-%m-%d %H:%M:%S') if row[7] else None,
                'sno': row[8],
                'student_name': row[9],
                'class_id': row[10],
                'course_name': row[11],
                'course_code': row[12],
                'class_name': row[13],
                'class_grade': row[14],
                'class_display': f"{row[14]}级{row[13]}" if row[13] and row[14] else "未分配班级"
            })

        cursor.close()
        result_data = {
            'items': scores,
            'pagination': {
                'current_page': page,
                'page_size': page_size,
                'total_count': total_count,
                'total_pages': total_pages,
                'has_prev': page > 1,
                'has_next': page < total_pages
            }
        }

        return success_response(msg="获取成绩列表成功", data=result_data)
    except Exception as e:
        print(f"获取成绩列表出错: {e}")
        return error_response(msg="获取成绩列表失败", error=str(e))

# 添加成绩
@app.route("/api/scores", methods=['POST'])
def api_add_score():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        data = request.get_json()

        if not data.get('student_id') or not data.get('course_id'):
            return error_response(msg="数据验证失败", error="学生和课程为必填项")

        # 验证成绩范围
        score = data.get('score')
        if score is not None:
            try:
                score = float(score)
                if score < 0 or score > 100:
                    return error_response(msg="数据验证失败", error="成绩必须在0-100之间")
            except (ValueError, TypeError):
                return error_response(msg="数据验证失败", error="成绩必须是有效数字")

        cursor = g.db.cursor()

        # 检查学生是否存在
        student_check_sql = "SELECT id FROM student WHERE id = %s"
        cursor.execute(student_check_sql, (data['student_id'],))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="添加成绩失败", error="所选学生不存在")

        # 检查课程是否存在
        course_check_sql = "SELECT id FROM course WHERE id = %s"
        cursor.execute(course_check_sql, (data['course_id'],))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="添加成绩失败", error="所选课程不存在")

        # 检查是否已存在该学生该课程的成绩
        existing_check_sql = "SELECT id FROM score WHERE student_id = %s AND course_id = %s"
        cursor.execute(existing_check_sql, (data['student_id'], data['course_id']))
        if cursor.fetchone():
            cursor.close()
            return error_response(msg="添加成绩失败", error="该学生该课程的成绩已存在")

        # 插入新成绩记录
        insert_sql = """
        INSERT INTO score (student_id, course_id, score, exam_date, remark, created_time, updated_time)
        VALUES (%s, %s, %s, %s, %s, NOW(), NOW())
        """
        cursor.execute(insert_sql, (
            data['student_id'],
            data['course_id'],
            score,
            data.get('exam_date') if data.get('exam_date') else None,
            data.get('remark')
        ))

        score_id = cursor.lastrowid
        g.db.commit()
        cursor.close()

        new_score = {
            'id': score_id,
            'student_id': data['student_id'],
            'course_id': data['course_id'],
            'score': score,
            'exam_date': data.get('exam_date'),
            'remark': data.get('remark')
        }

        return success_response(msg="成绩添加成功", data=new_score)

    except Exception as e:
        print(f"添加成绩出错: {e}")
        error_msg = str(e)
        return error_response(msg="添加成绩失败", error=error_msg)

# 更新成绩
@app.route("/api/scores/<int:score_id>", methods=['PUT'])
def api_update_score(score_id):
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        data = request.get_json()

        if not data.get('student_id') or not data.get('course_id'):
            return error_response(msg="数据验证失败", error="学生和课程为必填项")

        # 验证成绩范围
        score = data.get('score')
        if score is not None:
            try:
                score = float(score)
                if score < 0 or score > 100:
                    return error_response(msg="数据验证失败", error="成绩必须在0-100之间")
            except (ValueError, TypeError):
                return error_response(msg="数据验证失败", error="成绩必须是有效数字")

        cursor = g.db.cursor()

        # 检查成绩记录是否存在
        check_sql = "SELECT id FROM score WHERE id = %s"
        cursor.execute(check_sql, (score_id,))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="更新失败", error="成绩记录不存在")

        # 检查学生是否存在
        student_check_sql = "SELECT id FROM student WHERE id = %s"
        cursor.execute(student_check_sql, (data['student_id'],))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="更新成绩失败", error="所选学生不存在")

        # 检查课程是否存在
        course_check_sql = "SELECT id FROM course WHERE id = %s"
        cursor.execute(course_check_sql, (data['course_id'],))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="更新成绩失败", error="所选课程不存在")

        # 检查是否与其他成绩记录冲突（同一学生同一课程）
        conflict_sql = "SELECT id FROM score WHERE student_id = %s AND course_id = %s AND id != %s"
        cursor.execute(conflict_sql, (data['student_id'], data['course_id'], score_id))
        if cursor.fetchone():
            cursor.close()
            return error_response(msg="更新成绩失败", error="该学生该课程的成绩已存在")

        # 执行更新
        update_sql = """
        UPDATE score
        SET student_id = %s, course_id = %s, score = %s, exam_date = %s, remark = %s, updated_time = NOW()
        WHERE id = %s
        """
        cursor.execute(update_sql, (
            data['student_id'],
            data['course_id'],
            score,
            data.get('exam_date') if data.get('exam_date') else None,
            data.get('remark'),
            score_id
        ))

        g.db.commit()
        cursor.close()

        updated_score = {
            'id': score_id,
            'student_id': data['student_id'],
            'course_id': data['course_id'],
            'score': score,
            'exam_date': data.get('exam_date'),
            'remark': data.get('remark')
        }

        return success_response(msg="成绩更新成功", data=updated_score)

    except Exception as e:
        print(f"更新成绩出错: {e}")
        error_msg = str(e)
        return error_response(msg="更新成绩失败", error=error_msg)

# 删除成绩
@app.route("/api/scores/<int:score_id>", methods=['DELETE'])
def api_delete_score(score_id):
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        check_sql = "SELECT id FROM score WHERE id = %s"
        cursor.execute(check_sql, (score_id,))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="删除失败", error="成绩记录不存在")

        # 删除成绩
        delete_sql = "DELETE FROM score WHERE id = %s"
        cursor.execute(delete_sql, (score_id,))

        g.db.commit()
        cursor.close()

        delete_result = {
            'score_id': score_id
        }

        return success_response(msg="成绩删除成功", data=delete_result)

    except Exception as e:
        print(f"删除成绩出错: {e}")
        return error_response(msg="删除成绩失败", error=str(e))

# 获取学生选项列表（用于成绩管理）
@app.route("/api/students/options", methods=['GET'])
def api_get_student_options():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        sql = """
        SELECT s.id, s.sno, s.name, s.class_id, c.name as class_name, c.grade as class_grade
        FROM student s
        LEFT JOIN class c ON s.class_id = c.id
        ORDER BY c.grade DESC, c.name ASC, s.sno ASC
        """
        cursor.execute(sql)

        students = []
        for row in cursor.fetchall():
            students.append({
                'id': row[0],
                'sno': row[1],
                'name': row[2],
                'class_id': row[3],
                'class_name': row[4],
                'class_grade': row[5],
                'display': f"{row[1]} - {row[2]} ({row[5]}级{row[4]})" if row[4] and row[5] else f"{row[1]} - {row[2]} (未分配班级)"
            })

        cursor.close()
        return success_response(msg="获取学生选项成功", data=students)

    except Exception as e:
        print(f"获取学生选项出错: {e}")
        return error_response(msg="获取学生选项失败", error=str(e))

# 获取课程选项列表（用于成绩管理）
@app.route("/api/courses/options", methods=['GET'])
def api_get_course_options():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        sql = """
        SELECT id, name, code, credits
        FROM course
        ORDER BY name ASC
        """
        cursor.execute(sql)

        courses = []
        for row in cursor.fetchall():
            courses.append({
                'id': row[0],
                'name': row[1],
                'code': row[2],
                'credits': float(row[3]) if row[3] else 0,
                'display': f"{row[1]} ({row[2]})" if row[2] else row[1]
            })

        cursor.close()
        return success_response(msg="获取课程选项成功", data=courses)

    except Exception as e:
        print(f"获取课程选项出错: {e}")
        return error_response(msg="获取课程选项失败", error=str(e))





# 班级管理
@app.route("/admin/class")
def admin_class():
    return render_template("admin/class.html")
# 获取所有班级（分页查询）
@app.route("/api/classes", methods=['GET'])
def api_get_classes():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('page_size', 10))
        search = request.args.get('search', '').strip()
        grade_filter = request.args.get('grade', '').strip()

        page = max(1, page)
        page_size = min(max(1, page_size), 100)

        cursor = g.db.cursor()

        where_conditions = []
        params = []

        if search:
            where_conditions.append("name LIKE %s")
            search_param = f"%{search}%"
            params.append(search_param)

        if grade_filter:
            where_conditions.append("grade = %s")
            params.append(grade_filter)

        where_clause = ""
        if where_conditions:
            where_clause = "WHERE " + " AND ".join(where_conditions)

        count_sql = f"SELECT COUNT(*) FROM class {where_clause}"
        cursor.execute(count_sql, params)
        total_count = cursor.fetchone()[0]

        total_pages = (total_count + page_size - 1) // page_size
        offset = (page - 1) * page_size

        data_sql = f"""
        SELECT id, name, grade, created_time, updated_time
        FROM class
        {where_clause}
        ORDER BY created_time DESC
        LIMIT %s OFFSET %s
        """
        cursor.execute(data_sql, params + [page_size, offset])

        classes = []
        for row in cursor.fetchall():
            classes.append({
                'id': row[0],
                'name': row[1],
                'grade': row[2],
                'created_time': row[3].strftime('%Y-%m-%d %H:%M:%S') if row[3] else None,
                'updated_time': row[4].strftime('%Y-%m-%d %H:%M:%S') if row[4] else None
            })

        cursor.close()
        result_data = {
            'items': classes,
            'pagination': {
                'current_page': page,
                'page_size': page_size,
                'total_count': total_count,
                'total_pages': total_pages,
                'has_prev': page > 1,
                'has_next': page < total_pages
            }
        }

        return success_response(msg="获取班级列表成功", data=result_data)
    except Exception as e:
        print(f"获取班级列表出错: {e}")
        return error_response(msg="获取班级列表失败", error=str(e))
# 添加班级
@app.route("/api/classes", methods=['POST'])
def api_add_class():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        data = request.get_json()

        if not data.get('name') or not data.get('grade'):
            return error_response(msg="数据验证失败", error="班级名称和年级为必填项")

        cursor = g.db.cursor()

        # 检查班级名称和年级组合是否已存在
        check_sql = "SELECT id FROM class WHERE name = %s AND grade = %s"
        cursor.execute(check_sql, (data['name'], data['grade']))
        existing_class = cursor.fetchone()
        if existing_class:
            cursor.close()
            return error_response(msg="添加班级失败", error=f"年级 {data['grade']} 的班级 {data['name']} 已存在")

        # 插入新班级记录
        insert_sql = """
        INSERT INTO class (name, grade, created_time, updated_time)
        VALUES (%s, %s, NOW(), NOW())
        """
        cursor.execute(insert_sql, (data['name'], data['grade']))

        class_id = cursor.lastrowid
        g.db.commit()
        cursor.close()

        new_class = {
            'id': class_id,
            'name': data['name'],
            'grade': data['grade']
        }

        return success_response(msg="班级添加成功", data=new_class)

    except Exception as e:
        print(f"添加班级出错: {e}")
        error_msg = str(e)
        return error_response(msg="添加班级失败", error=error_msg)
# 更新班级
@app.route("/api/classes/<int:class_id>", methods=['PUT'])
def api_update_class(class_id):
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        data = request.get_json()

        if not data.get('name') or not data.get('grade'):
            return error_response(msg="数据验证失败", error="班级名称和年级为必填项")

        cursor = g.db.cursor()

        # 检查班级是否存在
        check_sql = "SELECT id FROM class WHERE id = %s"
        cursor.execute(check_sql, (class_id,))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="更新失败", error="班级不存在")

        # 检查是否与其他班级冲突
        conflict_sql = "SELECT id FROM class WHERE name = %s AND grade = %s AND id != %s"
        cursor.execute(conflict_sql, (data['name'], data['grade'], class_id))
        conflicting_class = cursor.fetchone()
        if conflicting_class:
            cursor.close()
            return error_response(msg="更新班级失败", error=f"年级 {data['grade']} 的班级 {data['name']} 已存在")

        # 执行更新
        update_sql = """
        UPDATE class
        SET name = %s, grade = %s, updated_time = NOW()
        WHERE id = %s
        """
        cursor.execute(update_sql, (data['name'], data['grade'], class_id))

        g.db.commit()
        cursor.close()

        updated_class = {
            'id': class_id,
            'name': data['name'],
            'grade': data['grade']
        }

        return success_response(msg="班级更新成功", data=updated_class)

    except Exception as e:
        print(f"更新班级出错: {e}")
        error_msg = str(e)
        return error_response(msg="更新班级失败", error=error_msg)
# 删除班级
@app.route("/api/classes/<int:class_id>", methods=['DELETE'])
def api_delete_class(class_id):
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        check_sql = "SELECT id FROM class WHERE id = %s"
        cursor.execute(check_sql, (class_id,))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="删除失败", error="班级不存在")

        student_check_sql = "SELECT COUNT(*) FROM student WHERE class_id = %s"
        cursor.execute(student_check_sql, (class_id,))
        student_count = cursor.fetchone()[0]

        score_count = 0
        if student_count > 0:
            score_check_sql = """
            SELECT COUNT(*) FROM score
            WHERE student_id IN (SELECT id FROM student WHERE class_id = %s)
            """
            cursor.execute(score_check_sql, (class_id,))
            score_count = cursor.fetchone()[0]

            if score_count > 0:
                delete_scores_sql = """
                DELETE FROM score
                WHERE student_id IN (SELECT id FROM student WHERE class_id = %s)
                """
                cursor.execute(delete_scores_sql, (class_id,))

            delete_students_sql = "DELETE FROM student WHERE class_id = %s"
            cursor.execute(delete_students_sql, (class_id,))

        delete_sql = "DELETE FROM class WHERE id = %s"
        cursor.execute(delete_sql, (class_id,))

        g.db.commit()
        cursor.close()

        message = f'班级删除成功'
        if student_count > 0:
            message += f'，同时删除了 {student_count} 名学生'
        if score_count > 0:
            message += f'和 {score_count} 条成绩记录'

        delete_result = {
            'class_id': class_id,
            'deleted_students_count': student_count,
            'deleted_scores_count': score_count
        }

        return success_response(msg=message, data=delete_result)

    except Exception as e:
        print(f"删除班级出错: {e}")
        return error_response(msg="删除班级失败", error=str(e))





# 课程管理
@app.route("/admin/course")
def admin_course():
    return render_template("admin/course.html")
# 获取所有课程（分页查询）
@app.route("/api/courses", methods=['GET'])
def api_get_courses():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        # 获取分页参数
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('page_size', 10))
        search = request.args.get('search', '').strip()
        credits_filter = request.args.get('credits', '').strip()

        page = max(1, page)
        page_size = min(max(1, page_size), 100)  # 限制最大页面大小为100

        cursor = g.db.cursor()

        where_conditions = []
        params = []

        if search:
            where_conditions.append("(name LIKE %s OR code LIKE %s)")
            search_param = f"%{search}%"
            params.extend([search_param, search_param])

        if credits_filter:
            where_conditions.append("credits = %s")
            params.append(float(credits_filter))

        where_clause = ""
        if where_conditions:
            where_clause = "WHERE " + " AND ".join(where_conditions)

        # 获取总数
        count_sql = f"SELECT COUNT(*) FROM course {where_clause}"
        cursor.execute(count_sql, params)
        total_count = cursor.fetchone()[0]

        # 计算分页信息
        total_pages = (total_count + page_size - 1) // page_size
        offset = (page - 1) * page_size

        # 获取分页数据
        data_sql = f"""
        SELECT id, name, code, credits, description, created_time, updated_time
        FROM course
        {where_clause}
        ORDER BY created_time DESC
        LIMIT %s OFFSET %s
        """
        cursor.execute(data_sql, params + [page_size, offset])

        courses = []
        for row in cursor.fetchall():
            courses.append({
                'id': row[0],
                'name': row[1],
                'code': row[2],
                'credits': float(row[3]) if row[3] else 0,
                'description': row[4],
                'created_time': row[5].strftime('%Y-%m-%d %H:%M:%S') if row[5] else None,
                'updated_time': row[6].strftime('%Y-%m-%d %H:%M:%S') if row[6] else None
            })

        cursor.close()
        result_data = {
            'items': courses,
            'pagination': {
                'current_page': page,
                'page_size': page_size,
                'total_count': total_count,
                'total_pages': total_pages,
                'has_prev': page > 1,
                'has_next': page < total_pages
            }
        }

        return success_response(msg="获取课程列表成功", data=result_data)
    except Exception as e:
        print(f"获取课程列表出错: {e}")
        return error_response(msg="获取课程列表失败", error=str(e))
# 添加课程
@app.route("/api/courses", methods=['POST'])
def api_add_course():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        data = request.get_json()

        # 验证必填字段
        if not data.get('name') or not data.get('code') or not data.get('credits'):
            return error_response(msg="数据验证失败", error="课程名称、代码和学分为必填项")

        cursor = g.db.cursor()

        # 检查课程名称是否已存在
        check_sql = "SELECT id FROM course WHERE name = %s OR code = %s"
        cursor.execute(check_sql, (data['name'], data['code']))
        if cursor.fetchone():
            cursor.close()
            return error_response(msg="添加课程失败", error="课程名称或代码已存在")

        # 插入新课程
        insert_sql = """
        INSERT INTO course (name, code, credits, description, created_time, updated_time)
        VALUES (%s, %s, %s, %s, NOW(), NOW())
        """
        cursor.execute(insert_sql, (
            data['name'],
            data['code'],
            data['credits'],
            data.get('description')
        ))

        course_id = cursor.lastrowid
        g.db.commit()
        cursor.close()

        new_course = {
            'id': course_id,
            'name': data['name'],
            'code': data['code'],
            'credits': data['credits'],
            'description': data.get('description')
        }

        return success_response(msg="课程添加成功", data=new_course)

    except Exception as e:
        print(f"添加课程出错: {e}")
        return error_response(msg="添加课程失败", error=str(e))
# 更新课程
@app.route("/api/courses/<int:course_id>", methods=['PUT'])
def api_update_course(course_id):
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        data = request.get_json()

        # 验证必填字段
        if not data.get('name') or not data.get('code') or not data.get('credits'):
            return error_response(msg="数据验证失败", error="课程名称、代码和学分为必填项")

        cursor = g.db.cursor()

        # 检查课程是否存在
        check_sql = "SELECT id FROM course WHERE id = %s"
        cursor.execute(check_sql, (course_id,))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="更新失败", error="课程不存在")

        # 检查名称或代码是否与其他课程冲突
        conflict_sql = "SELECT id FROM course WHERE (name = %s OR code = %s) AND id != %s"
        cursor.execute(conflict_sql, (data['name'], data['code'], course_id))
        if cursor.fetchone():
            cursor.close()
            return error_response(msg="更新课程失败", error="课程名称或代码已存在")

        # 更新课程
        update_sql = """
        UPDATE course
        SET name = %s, code = %s, credits = %s, description = %s, updated_time = NOW()
        WHERE id = %s
        """
        cursor.execute(update_sql, (
            data['name'],
            data['code'],
            data['credits'],
            data.get('description'),
            course_id
        ))

        g.db.commit()
        cursor.close()

        updated_course = {
            'id': course_id,
            'name': data['name'],
            'code': data['code'],
            'credits': data['credits'],
            'description': data.get('description')
        }

        return success_response(msg="课程更新成功", data=updated_course)

    except Exception as e:
        print(f"更新课程出错: {e}")
        return error_response(msg="更新课程失败", error=str(e))
# 删除课程
@app.route("/api/courses/<int:course_id>", methods=['DELETE'])
def api_delete_course(course_id):
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        # 检查课程是否存在
        check_sql = "SELECT id FROM course WHERE id = %s"
        cursor.execute(check_sql, (course_id,))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="删除失败", error="课程不存在")

        # 检查是否有相关的成绩记录
        score_check_sql = "SELECT COUNT(*) FROM score WHERE course_id = %s"
        cursor.execute(score_check_sql, (course_id,))
        score_count = cursor.fetchone()[0]

        if score_count > 0:
            # 如果有成绩记录，先删除相关成绩
            delete_scores_sql = "DELETE FROM score WHERE course_id = %s"
            cursor.execute(delete_scores_sql, (course_id,))

        # 删除课程
        delete_sql = "DELETE FROM course WHERE id = %s"
        cursor.execute(delete_sql, (course_id,))

        g.db.commit()
        cursor.close()

        message = f'课程删除成功'
        if score_count > 0:
            message += f'，同时删除了 {score_count} 条相关成绩记录'

        # 返回删除结果信息
        delete_result = {
            'course_id': course_id,
            'deleted_scores_count': score_count
        }

        return success_response(msg=message, data=delete_result)

    except Exception as e:
        print(f"删除课程出错: {e}")
        return error_response(msg="删除课程失败", error=str(e))









# 管理员管理
@app.route("/admin/admins")
def admin_admins():
    return render_template("admin/admins.html")
# 获取管理员列表（分页查询）
@app.route("/api/admins",methods=['GET'])
def api_get_admins():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        # 获取分页参数
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('page_size', 10))
        search = request.args.get('search', '').strip()

        page = max(1, page)
        page_size = min(max(1, page_size), 100)

        cursor = g.db.cursor()

        # 构建WHERE条件
        where_conditions = []
        params = []

        if search:
            where_conditions.append("username LIKE %s")
            params.append(f"%{search}%")

        where_clause = ""
        if where_conditions:
            where_clause = "WHERE " + " AND ".join(where_conditions)

        # 获取总数
        count_sql = f"SELECT COUNT(*) FROM admin {where_clause}"
        cursor.execute(count_sql, params)
        total_count = cursor.fetchone()[0]

        # 计算分页信息
        total_pages = (total_count + page_size - 1) // page_size
        offset = (page - 1) * page_size

        # 获取分页数据
        data_sql = f"""
        SELECT id, username, password, role
        FROM admin
        {where_clause}
        ORDER BY id DESC
        LIMIT %s OFFSET %s
        """
        cursor.execute(data_sql, params + [page_size, offset])

        admins = []
        for row in cursor.fetchall():
            admins.append({
                'id': row[0],
                'username': row[1],
                'password': row[2],
                'role': row[3]
            })

        cursor.close()

        # 返回分页数据和元信息
        result_data = {
            'items': admins,
            'pagination': {
                'current_page': page,
                'page_size': page_size,
                'total_count': total_count,
                'total_pages': total_pages,
                'has_prev': page > 1,
                'has_next': page < total_pages
            }
        }

        return success_response(msg="获取管理员列表成功", data=result_data)
    except Exception as e:
        print(f"获取管理员列表出错: {e}")
        return error_response(msg="获取管理员列表失败", error=str(e))
# 添加
@app.route("/api/admins", methods=['POST'])
def api_add_admins():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        data = request.get_json()

        # 验证必填字段
        if not data.get('username') or not data.get('password'):
            return error_response(msg="数据验证失败", error="用户名、密码为必填项")

        cursor = g.db.cursor()

        # 检查名称是否已存在
        check_sql = "SELECT id FROM admin WHERE username = %s "
        cursor.execute(check_sql, (data['username'], ))
        if cursor.fetchone():
            cursor.close()
            return error_response(msg="添加管理员失败", error="该用户名已存在")

        # 插入新管理员
        insert_sql = """
        INSERT INTO admin (username,password)
        VALUES (%s, %s)
        """
        cursor.execute(insert_sql, (
            data['username'],
            data['password']
        ))

        admin_id = cursor.lastrowid
        g.db.commit()
        cursor.close()

        new_admin = {
            'id': admin_id,
            'username': data['username'],
            'password':data['password']
        }

        return success_response(msg="管理员添加成功", data=new_admin)

    except Exception as e:
        print(f"添加管理员出错: {e}")
        return error_response(msg="添加管理员失败", error=str(e))
# 更新
@app.route("/api/admins/<int:admin_id>", methods=['PUT'])
def api_update_admins(admin_id):
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        data = request.get_json()

        # 验证必填字段
        if not data.get('username') or not data.get('password'):
            return error_response(msg="数据验证失败", error="用户名、密码为必填项")

        cursor = g.db.cursor()

        # 检查是否存在
        check_sql = "SELECT id FROM admin WHERE id = %s"
        cursor.execute(check_sql, (admin_id,))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="更新失败", error="管理员不存在")

        # 检查冲突
        conflict_sql = "SELECT id FROM admin WHERE username = %s AND id != %s"
        cursor.execute(conflict_sql, (data['username'], admin_id))
        if cursor.fetchone():
            cursor.close()
            return error_response(msg="更新管理员失败", error="用户名已存在")

        # 更新
        update_sql = """
        UPDATE admin
        SET username = %s, password = %s
        WHERE id = %s
        """
        cursor.execute(update_sql, (
            data['username'],
            data['password'],
            admin_id
        ))

        g.db.commit()
        cursor.close()

        updated_admin = {
            'id': admin_id,
            'username': data['username'],
            'password': data['password'],
        }

        return success_response(msg="管理员更新成功", data=updated_admin)

    except Exception as e:
        print(f"更新管理员出错: {e}")
        return error_response(msg="更新管理员失败", error=str(e))
# 删除
@app.route("/api/admins/<int:admin_id>", methods=['DELETE'])
def api_delete_admins(admin_id):
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        # 检查是否存在
        check_sql = "SELECT id FROM admin WHERE id = %s"
        cursor.execute(check_sql, (admin_id,))
        if not cursor.fetchone():
            cursor.close()
            return error_response(msg="删除失败", error="管理员不存在")

        # 删除
        delete_sql = "DELETE FROM admin WHERE id = %s"
        cursor.execute(delete_sql, (admin_id,))

        g.db.commit()
        cursor.close()

        # 返回删除结果信息
        delete_result = {
            'admin_id': admin_id,
        }

        return success_response(msg='管理员删除成功', data=delete_result)

    except Exception as e:
        print(f"删除管理员出错: {e}")
        return error_response(msg="删除管理员失败", error=str(e))




# 图表数据
# 1. 成绩分布柱状图数据
@app.route("/api/charts/score-distribution", methods=['GET'])
def api_chart_score_distribution():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        # 查询各科目的成绩分布
        sql = """
        SELECT
            c.name as course_name,
            SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) as excellent,
            SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END) as good,
            SUM(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END) as medium,
            SUM(CASE WHEN sc.score >= 60 AND sc.score < 70 THEN 1 ELSE 0 END) as pass,
            SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) as fail
        FROM course c
        LEFT JOIN score sc ON c.id = sc.course_id
        WHERE sc.score IS NOT NULL
        GROUP BY c.id, c.name
        ORDER BY c.name
        """
        cursor.execute(sql)

        courses = []
        excellent_data = []
        good_data = []
        medium_data = []
        pass_data = []
        fail_data = []

        for row in cursor.fetchall():
            courses.append(row[0])
            excellent_data.append(row[1])
            good_data.append(row[2])
            medium_data.append(row[3])
            pass_data.append(row[4])
            fail_data.append(row[5])

        cursor.close()

        chart_data = {
            'courses': courses,
            'series': [
                {'name': '优秀(90+)', 'data': excellent_data},
                {'name': '良好(80-89)', 'data': good_data},
                {'name': '中等(70-79)', 'data': medium_data},
                {'name': '及格(60-69)', 'data': pass_data},
                {'name': '不及格(<60)', 'data': fail_data}
            ]
        }

        return success_response(msg="获取成绩分布数据成功", data=chart_data)

    except Exception as e:
        print(f"获取成绩分布数据出错: {e}")
        return error_response(msg="获取成绩分布数据失败", error=str(e))

# 2. 班级平均分对比雷达图数据
@app.route("/api/charts/class-radar", methods=['GET'])
def api_chart_class_radar():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        # 获取课程列表
        cursor.execute("SELECT id, name FROM course ORDER BY name")
        courses = cursor.fetchall()
        course_names = [course[1] for course in courses]

        # 获取班级平均分数据
        sql = """
        SELECT
            cl.grade, cl.name as class_name,
            c.id as course_id, c.name as course_name,
            ROUND(AVG(sc.score), 2) as avg_score
        FROM class cl
        LEFT JOIN student s ON cl.id = s.class_id
        LEFT JOIN score sc ON s.id = sc.student_id
        LEFT JOIN course c ON sc.course_id = c.id
        WHERE sc.score IS NOT NULL
        GROUP BY cl.id, cl.grade, cl.name, c.id, c.name
        ORDER BY cl.grade DESC, cl.name, c.name
        """
        cursor.execute(sql)

        class_data = {}
        for row in cursor.fetchall():
            class_key = f"{row[0]}级{row[1]}"
            if class_key not in class_data:
                class_data[class_key] = {}
            class_data[class_key][row[3]] = row[4] if row[4] else 0

        cursor.close()

        # 格式化为雷达图数据
        radar_data = []
        for class_name, scores in class_data.items():
            class_scores = []
            for course_name in course_names:
                class_scores.append(scores.get(course_name, 0))
            radar_data.append({
                'name': class_name,
                'value': class_scores
            })

        chart_data = {
            'indicator': [{'name': name, 'max': 100} for name in course_names],
            'series': radar_data[:8]  # 限制显示前8个班级，避免图表过于拥挤
        }

        return success_response(msg="获取班级雷达图数据成功", data=chart_data)

    except Exception as e:
        print(f"获取班级雷达图数据出错: {e}")
        return error_response(msg="获取班级雷达图数据失败", error=str(e))

# 3. 年级成绩趋势折线图数据
@app.route("/api/charts/grade-trend", methods=['GET'])
def api_chart_grade_trend():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        # 获取年级平均分数据
        sql = """
        SELECT
            cl.grade,
            c.name as course_name,
            ROUND(AVG(sc.score), 2) as avg_score
        FROM class cl
        LEFT JOIN student s ON cl.id = s.class_id
        LEFT JOIN score sc ON s.id = sc.student_id
        LEFT JOIN course c ON sc.course_id = c.id
        WHERE sc.score IS NOT NULL
        GROUP BY cl.grade, c.id, c.name
        ORDER BY cl.grade, c.name
        """
        cursor.execute(sql)

        grade_data = {}
        courses = set()

        for row in cursor.fetchall():
            grade = row[0]
            course = row[1]
            avg_score = row[2] if row[2] else 0

            courses.add(course)
            if grade not in grade_data:
                grade_data[grade] = {}
            grade_data[grade][course] = avg_score

        cursor.close()

        # 格式化数据
        sorted_grades = sorted(grade_data.keys())
        sorted_courses = sorted(list(courses))

        series_data = []
        for course in sorted_courses:
            course_scores = []
            for grade in sorted_grades:
                course_scores.append(grade_data[grade].get(course, 0))
            series_data.append({
                'name': course,
                'data': course_scores
            })

        chart_data = {
            'grades': sorted_grades,
            'series': series_data
        }

        return success_response(msg="获取年级趋势数据成功", data=chart_data)

    except Exception as e:
        print(f"获取年级趋势数据出错: {e}")
        return error_response(msg="获取年级趋势数据失败", error=str(e))

# 4. 学生成绩排名表格数据
@app.route("/api/charts/student-ranking", methods=['GET'])
def api_chart_student_ranking():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        # 获取学生总分和排名
        sql = """
        SELECT
            s.sno, s.name as student_name,
            cl.grade, cl.name as class_name,
            ROUND(AVG(sc.score), 2) as avg_score,
            ROUND(SUM(sc.score * c.credits) / SUM(c.credits), 2) as weighted_avg,
            COUNT(sc.id) as course_count
        FROM student s
        LEFT JOIN class cl ON s.class_id = cl.id
        LEFT JOIN score sc ON s.id = sc.student_id
        LEFT JOIN course c ON sc.course_id = c.id
        WHERE sc.score IS NOT NULL
        GROUP BY s.id, s.sno, s.name, cl.grade, cl.name
        HAVING course_count > 0
        ORDER BY weighted_avg DESC
        LIMIT 50
        """
        cursor.execute(sql)

        ranking_data = []
        rank = 1
        for row in cursor.fetchall():
            ranking_data.append({
                'rank': rank,
                'sno': row[0],
                'name': row[1],
                'class': f"{row[2]}级{row[3]}",
                'avg_score': row[4],
                'weighted_avg': row[5],
                'course_count': row[6]
            })
            rank += 1

        cursor.close()

        return success_response(msg="获取学生排名数据成功", data=ranking_data)

    except Exception as e:
        print(f"获取学生排名数据出错: {e}")
        return error_response(msg="获取学生排名数据失败", error=str(e))

# 5. 课程学分与平均分散点图数据
@app.route("/api/charts/course-scatter", methods=['GET'])
def api_chart_course_scatter():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        # 获取课程学分与平均分数据
        sql = """
        SELECT
            c.name as course_name,
            c.credits,
            ROUND(AVG(sc.score), 2) as avg_score,
            COUNT(sc.id) as student_count
        FROM course c
        LEFT JOIN score sc ON c.id = sc.course_id
        WHERE sc.score IS NOT NULL
        GROUP BY c.id, c.name, c.credits
        ORDER BY c.credits, avg_score
        """
        cursor.execute(sql)

        scatter_data = []
        for row in cursor.fetchall():
            scatter_data.append({
                'name': row[0],
                'value': [float(row[1]), row[2], row[3]],  # [学分, 平均分, 学生数量]
                'credits': float(row[1]),
                'avg_score': row[2],
                'student_count': row[3]
            })

        cursor.close()

        return success_response(msg="获取课程散点图数据成功", data=scatter_data)

    except Exception as e:
        print(f"获取课程散点图数据出错: {e}")
        return error_response(msg="获取课程散点图数据失败", error=str(e))

# 6. 班级人数与成绩分布饼图数据
@app.route("/api/charts/class-pie", methods=['GET'])
def api_chart_class_pie():
    if 'username' not in session and 'sno' not in session:
        return error_response(msg="访问被拒绝", error="请先登录")

    try:
        cursor = g.db.cursor()

        # 获取班级人数和优秀率数据
        sql = """
        SELECT
            cl.grade, cl.name as class_name,
            COUNT(DISTINCT s.id) as student_count,
            COUNT(DISTINCT CASE WHEN sc.score >= 85 THEN s.id END) as excellent_students,
            ROUND(COUNT(DISTINCT CASE WHEN sc.score >= 85 THEN s.id END) * 100.0 / COUNT(DISTINCT s.id), 2) as excellent_rate
        FROM class cl
        LEFT JOIN student s ON cl.id = s.class_id
        LEFT JOIN score sc ON s.id = sc.student_id
        WHERE s.id IS NOT NULL
        GROUP BY cl.id, cl.grade, cl.name
        HAVING student_count > 0
        ORDER BY excellent_rate DESC
        """
        cursor.execute(sql)

        class_pie_data = []
        for row in cursor.fetchall():
            class_pie_data.append({
                'name': f"{row[0]}级{row[1]}",
                'value': row[2],  # 学生人数
                'excellent_count': row[3],
                'excellent_rate': row[4] if row[4] else 0
            })

        cursor.close()

        return success_response(msg="获取班级饼图数据成功", data=class_pie_data)

    except Exception as e:
        print(f"获取班级饼图数据出错: {e}")
        return error_response(msg="获取班级饼图数据失败", error=str(e))



if __name__ == '__main__':
    app.run(debug=True)


